BEGIN;

-- 视图依赖列类型，先移除视图
DROP VIEW IF EXISTS activity_scores_overview;

ALTER TABLE evaluation ALTER COLUMN verdict DROP DEFAULT;

ALTER TABLE evaluation
  ALTER COLUMN verdict TYPE BOOLEAN
  USING CASE
    WHEN verdict::text = 'veto' THEN TRUE
    ELSE FALSE
  END;

ALTER TABLE evaluation ALTER COLUMN verdict SET NOT NULL;
ALTER TABLE evaluation ALTER COLUMN verdict SET DEFAULT FALSE;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE udt_name = 'evaluation_verdict'
  ) THEN
    BEGIN
      EXECUTE 'DROP TYPE IF EXISTS evaluation_verdict';
    EXCEPTION WHEN others THEN
      NULL;
    END;
  END IF;
END $$;

-- 按新类型重建视图
CREATE OR REPLACE VIEW activity_scores_overview AS
SELECT a.id AS activity_id,
       a.name AS activity_name,
       a.latitude,
       a.longitude,
       a.location_admin,
       a.location_detail,
       e.score_total,
       e.verdict,
       e.level,
       e.version_no
FROM activity a
LEFT JOIN LATERAL (
    SELECT ev.*
    FROM evaluation ev
    WHERE ev.activity_id = a.id
    ORDER BY ev.version_no DESC, ev.created_at DESC
    LIMIT 1
) e ON TRUE;

COMMIT;